# @Time    : 2019/7/30 14:31
# @Author  : patrick
# @File    : xlsx_utils.py
# @Software: PyCharm
from openpyxl import load_workbook


class XlsxUtils:
    def __init__(self):
        """
        初始化函数
        """
        self.filename = r"D:\04Table_For IT_TL_IFA_0509.xlsx"
        # 载入表格数据与公式
        self.load_workbook = load_workbook(filename=self.filename)
        self.result = []

    def get_sheet_data(self):
        """
        获取表格数据函数
        :return: 返回费率计算表格中的关键参数
        """
        # data_only意为载入纯数据，不载入公式
        wb = load_workbook(filename=self.filename, data_only=True)
        sheet = wb["Sample"]
        for i in range(1, 9):
            self.result.append(sheet.cell(row=i, column=3).value)
        wb.close()
        for i in range(self.result.count()):
            print(self.result[i])
        return self.result

    def set_sheet_value(self, **args):
        """
        自定义费率组合参数函数
        :param args: 费率计算excel表格中的键值对，例：PPP=10
        :return: 无返回
        """
        wb = self.load_workbook
        sheet = wb["Sample"]
        if args.get("PPP") != "" and args.get("PPP") is not None:
            sheet.cell(row=1, column=3).value = args.get("PPP")
        if args.get("Age") != "" and args.get("Age") is not None:
            sheet.cell(row=2, column=3).value = args.get("Age")
        if args.get("Sex") != "" and args.get("Sex") is not None:
            sheet.cell(row=3, column=3).value = args.get("Sex")
        if args.get("BP") != "" and args.get("BP") is not None:
            sheet.cell(row=6, column=3).value = args.get("BP")
        if args.get("Paymode") != "" and args.get("Paymode") is not None:
            sheet.cell(row=7, column=3).value = args.get("Paymode")
        wb.save(self.filename)
        print("费率参数组合设置完成")
        wb.close()


if __name__ == '__main__':
    test = XlsxUtils()
    test.set_sheet_value(PPP=5, Paymode="M")
